---------------------------------------------------
-- @name: cpu_cost_basic
-- @author: dion cho
-- @description: test for concept of CBO
---------------------------------------------------

@capture_on

@version

-- create objects
drop table t1 purge;

create table t1(
  c1 int, 
  c2 char(100), 
  c3 char(100)
);

create index t1_n1 on t1(c1);


-- generate data and gather statistics
insert into t1
select 
  level, 
  'dummy', 
  'dummy' 
from dual
connect by level <= 100000
;

@gather t1


--------------------------------------------
-- case1: io model vs. cpu model

-- check the disparity of cost
-- when cost model is io

alter session set "_optimizer_cost_model" = io;

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 1000
;

@plan

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 2000
;

@plan


-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 10000
;

@plan


-- when cost model is cpu and no system stats
alter session set "_optimizer_cost_model" = cpu;

exec dbms_stats.delete_system_stats;

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 1000
;

@plan

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 2000
;

@plan

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 10000
;

@plan


-- index or fts?
explain plan for
select /*+ full(t1) */ * 
from t1
where c1 between 1 and 10000
;

@plan


-- when cost model is cpu and with system stats
alter session set "_optimizer_cost_model" = cpu;

begin
  dbms_stats.set_system_stats('cpuspeed', 1680);
  dbms_stats.set_system_stats('sreadtim', 5);
  dbms_stats.set_system_stats('mreadtim', 10);
  dbms_stats.set_system_stats('mbrc', 8);
end;
/


-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 1000
;

@plan

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 2000
;

@plan

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 10000
;

@plan

-- index or fts?
explain plan for
select /*+ full(t1) */ * 
from t1
where c1 between 1 and 10000
;

@plan


-- when mreadtim got increased
alter session set "_optimizer_cost_model" = cpu;

begin
  dbms_stats.set_system_stats('cpuspeed', 1680);
  dbms_stats.set_system_stats('sreadtim', 5);
  dbms_stats.set_system_stats('mreadtim', 20);
  dbms_stats.set_system_stats('mbrc', 8);
end;
/


-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 1000
;

@plan

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 2000
;

@plan

-- index or fts?
explain plan for
select * 
from t1
where c1 between 1 and 10000
;

@plan

-- index or fts?
explain plan for
select /*+ full(t1) */ * 
from t1
where c1 between 1 and 10000
;

@plan



--------------------------------------------
-- case2: additional cpu cost

-- cost of full scan
explain plan for
select c1
from t1
;

@plan


-- additional cost by predicate
explain plan for
select *
from t1
where c2 = 'dummy'
;

@plan


-- additional cost by function predicate
explain plan for
select *
from t1
where upper(c2) = 'DUMMY'
;

@plan


-- additional cost by function predicate
explain plan for
select *
from t1
where lower(upper(c2)) = 'dummy'
;

@plan


-- how about the cost of row fetch?
explain plan for
select c1
from t1
;

@plan

explain plan for
select c2
from t1
;

@plan

explain plan for
select c1, c2
from t1
;

@plan

explain plan for
select c1, c2, c3
from t1
;

@plan


@capture_off
